# Copyright (c) 2025, hzmes and contributors
# For license information, please see license.txt

import frappe
from frappe import _


def execute(filters=None):
	# get_data要传参filters
	# message = []
	# message.append("<br>The value of the Creation To Filter is : " + filters.get("creation_to"))
	return get_columns(filters), get_data(filters)

def get_columns(filters):
	columns = [
		{
			"label": _("Project"),
			"fieldtype": "Data",
			"fieldname": "project",
			"options": "Project",
			"width": 100,
		},
		{
			"label": _("Project Name"),
			"fieldtype": "Data",
			"fieldname": "project_name",
			"width": 380,
		},
		{
			"label": _("Date"),
			"fieldtype": "Date",
			"fieldname": "date",
			"width": 150,
		},
		{
			"label": _("Current Schedule"),
			"fieldtype": "Data",
			"fieldname": "current_schedule",
			"width": 150,
		},
		{
			"label": _("Budget"),
			"fieldtype": "Currency",
			"fieldname": "budget",
			"width": 100,
		},
		{
			"label": _("Cost"),
			"fieldtype": "Currency",
			"fieldname": "cost",
			"width": 120,
		},
		{
			"label": _("Project Manager"),
			"fieldtype": "Data",
			"fieldname": "project_manager",
			"width": 100,
		},
	]
	return columns

def get_data(filters):
	# 获取filters
	# frappe.msgprint(f'the filter is {filters}')
	from_date, to_date = filters.get('from_date'), filters.get('to_date')
	# 先配置默认conditions再通过if判断是否增加条件项
	conditions = " AND 1=1 "
	total = []	# 默认空列表
	# 2025-05-08修订:根据财务张泳心建议, 把过滤条件从creation改为项目属性的expected_start_date
	sql_total = f""" 
		    SELECT tb1.project, tb2.project_name, tb2.expected_start_date as date, tb2.custom_current_schedule as current_schedule, tb2.custom_estimated_development_cost as budget, SUM(tb1.amount) as cost, tb2.custom_pm_fullname as project_manager FROM
			(
			SELECT detail_project_number as project, detail_expense_name as expense, SUM(amount) as amount FROM `tabGL Voucher Allocation` WHERE detail_project_number>='PROJ-0001' and detail_project_number<='ZZZZZZZZ' GROUP BY detail_project_number, detail_expense_name
			UNION ALL
			SELECT project, expense_claim_type as expense, SUM(amount) as amount FROM `tabExpense Allocation` WHERE project>='PROJ-0001' and project<='ZZZZZZZZ' GROUP BY project, expense_claim_type
			UNION ALL
			SELECT project, '材料费' as expense, SUM(total_outgoing_value) as amount FROM `tabStock Entry` WHERE stock_entry_type = 'Material Issue' AND project>='PROJ-0001' and project<='ZZZZZZZZ' GROUP BY project
			) tb1 LEFT JOIN `tabProject` tb2 on tb1.project = tb2.name
			WHERE tb2.expected_start_date>='{from_date}' AND tb2.expected_start_date<='{to_date}' {conditions}
			GROUP BY tb1.project
	 """
	# 计算每个项目的合计数
	total = frappe.db.sql(sql_total, as_dict=True)
	# if len(total) > 0:

	# 	sql_expense = """ SELECT DISTINCT tb1.expense FROM
	# 			(
	# 			SELECT detail_project_number as project, detail_expense_name as expense, SUM(amount) as amount FROM `tabGL Voucher Allocation` WHERE detail_project_number>='PROJ-0001' and detail_project_number<='ZZZZZZZZ' GROUP BY detail_project_number, detail_expense_name
	# 			UNION ALL
	# 			SELECT project, expense_claim_type as expense, SUM(amount) as amount FROM `tabExpense Allocation` WHERE project>='PROJ-0001' and project<='ZZZZZZZZ' GROUP BY project, expense_claim_type
	# 			UNION ALL
	# 			SELECT project, '材料费' as expense, SUM(total_outgoing_value) as amount FROM `tabStock Entry` WHERE stock_entry_type = 'Material Issue' AND project>='PROJ-0001' and project<='ZZZZZZZZ' GROUP BY project
	# 			) tb1
	# 	"""
	# 	# 计算有哪些项目明细
	# 	expenses = frappe.db.sql(sql_expense, as_dict=True)
	# 	if len(expenses) > 0:
	# 		# 往total元素中增加鍵值对: {"项目明细": 0}
	# 		for i in total:
	# 			for ex in expenses:
	# 				i[ex.expense] = 0	# 默认值=0

	# 		sql_detail = """ SELECT detail_project_number as project, detail_expense_name as expense, SUM(amount) as amount FROM `tabGL Voucher Allocation` WHERE detail_project_number>='PROJ-0001' and detail_project_number<='ZZZZZZZZ' GROUP BY detail_project_number, detail_expense_name
	# 			UNION ALL
	# 			SELECT project, expense_claim_type as expense, SUM(amount) as amount FROM `tabExpense Allocation` WHERE project>='PROJ-0001' and project<='ZZZZZZZZ' GROUP BY project, expense_claim_type
	# 			UNION ALL
	# 			SELECT project, '材料费' as expense, SUM(total_outgoing_value) as amount FROM `tabStock Entry` WHERE stock_entry_type = 'Material Issue' AND project>='PROJ-0001' and project<='ZZZZZZZZ' GROUP BY project
	# 		"""

	# 		# 计算全部项目明细
	# 		details = frappe.db.sql(sql_detail, as_dict=True)
	# 		if len(details) > 0:
	# 			for item in details:
	# 				for pro in total:
	# 					if pro.get('project') == item.project:
	# 						pro[item.expense] = item.amount

	return total
